package com.xxxy.jsq.dao;

import com.xxxy.jsq.dbhelper.C3P0Utils;
import com.xxxy.jsq.model.Page;
import com.xxxy.jsq.model.Teacher;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TeacherDao implements BaseDao<Teacher> {
    private C3P0Utils db = new C3P0Utils();
    @Override
    public String add(Teacher model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into pa_teacher(teacher_name, department_id, ");
        sqlStr.append("teacher_gender, teacher_phone, teacher_num,teacher_flag ) values(?,?,?,?,?,?)");

        // 检查同名教师（新增时不允许重名）
        boolean flag = isSame(model, true);
        if (!flag) {
            int num = 0;
            try {
                conn = db.getConnection();
                pstm = conn.prepareStatement(sqlStr.toString());
                pstm.setString(1, model.getTeacherName());
                pstm.setString(2, model.getDepartmentId());
                pstm.setString(3, model.getTeacherGender());
                pstm.setString(4, model.getTeacherPhone());
                pstm.setString(5, model.getTeacherNum());
                pstm.setString(6, model.getTeacherFlag());
                num = pstm.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
                return "Err: " + e.getMessage();
            }
            return num > 0 ? "Ok" : "Err";
        } else {
            return "Same";
        }
    }

    @Override
    public String update(Teacher model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        // 检查同名教师（更新时排除自身ID）
        if (isSame(model, false)) {
            return "Same";
        }

        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("update pa_teacher set ");
        // 动态拼接更新字段（仅更新非空值）
        if (model.getTeacherName() != null && !model.getTeacherName().equals("")) {
            sqlStr.append("teacher_name='" + model.getTeacherName() + "'");
        }
        if (model.getDepartmentId() != null && !model.getDepartmentId().equals("")) {
            sqlStr.append(", department_id='" + model.getDepartmentId() + "'");
        }
        if (model.getTeacherFlag() != null && !model.getTeacherFlag().equals("")) {
            sqlStr.append(", teacher_flag='" + model.getTeacherFlag() + "'");
        }
        if (model.getTeacherGender() != null && !model.getTeacherGender().equals("")) {
            sqlStr.append(", teacher_gender='" + model.getTeacherGender() + "'");
        }
        if (model.getTeacherPhone() != null && !model.getTeacherPhone().equals("")) {
            sqlStr.append(", teacher_phone='" + model.getTeacherPhone() + "'");
        }
        if (model.getTeacherNum() != null && !model.getTeacherNum().equals("")) {
            sqlStr.append(", teacher_num='" + model.getTeacherNum() + "'");
        }
        sqlStr.append(" where teacher_id=?");

        int num = 0;
        try {
            conn = db.getConnection();
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setLong(1, model.getTeacherId());
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }

    @Override
    public String delete(Teacher model) {
        return "";
    }

    @Override
    public String deleteById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        String sql = "delete from pa_teacher where teacher_id=?";

        int num = 0;
        try {
            conn = db.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setLong(1, cid);
            num = pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return num > 0 ? "Ok" : "Err";
    }

    @Override
    public List<Teacher> getAllData(Teacher model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        List<Teacher> list = new ArrayList<>();

        sqlStr.append("SELECT t.*, d.department_name as pdname FROM pa_teacher t ");
        sqlStr.append("LEFT JOIN pa_department d ON t.department_id = d.department_id ");
        sqlStr.append("where 1=1 ");

        // 动态拼接查询条件
        if (model.getTeacherName() != null && !model.getTeacherName().equals("")) {
            sqlStr.append("and t.teacher_name like '%" + model.getTeacherName() + "%' ");
        }
        if (model.getDepartmentId() != null && !model.getDepartmentId().equals("")) {
            sqlStr.append("and t.department_id = '" + model.getDepartmentId() + "' ");
        }
        if (model.getTeacherFlag() != null && !model.getTeacherFlag().equals("")) {
            sqlStr.append("and t.teacher_flag = '" + model.getTeacherFlag() + "' ");
        }

        sqlStr.append("ORDER BY t.teacher_stmp desc, t.teacher_id asc");

        try {
            conn = db.getConnection();
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTeacherId(rs.getLong("teacher_id"));
                teacher.setTeacherName(rs.getString("teacher_name"));
                teacher.setDepartmentId(rs.getString("department_id"));
                teacher.setTeacherFlag(rs.getString("teacher_flag").equals("0") ? "启用" : "禁用");
                teacher.setTeacherGender(rs.getString("teacher_gender"));
                teacher.setTeacherPhone(rs.getString("teacher_phone"));
                teacher.setTeacherNum(rs.getString("teacher_num"));
                teacher.setPdname(rs.getString("pdname"));  // 关联部门名称
                list.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Teacher findById(Long cid) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String sql = "SELECT t.*, d.department_name as pdname FROM pa_teacher t " +
                "LEFT JOIN pa_department d ON t.department_id = d.department_id " +
                "where t.teacher_id=?";

        Teacher teacher = new Teacher();
        try {
            conn = db.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setLong(1, cid);
            rs = pstm.executeQuery();
            if (rs.next()) {
                teacher.setTeacherId(rs.getLong("teacher_id"));
                teacher.setTeacherName(rs.getString("teacher_name"));
                teacher.setDepartmentId(rs.getString("department_id"));
                teacher.setTeacherFlag(rs.getString("teacher_flag").equals("0") ? "启用" : "禁用");
                teacher.setTeacherGender(rs.getString("teacher_gender"));
                teacher.setTeacherPhone(rs.getString("teacher_phone"));
                teacher.setTeacherNum(rs.getString("teacher_num"));
                teacher.setPdname(rs.getString("pdname"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teacher;
    }

    @Override
    public Teacher find(Teacher model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT * FROM pa_teacher where 1=1 ");

        // 精确匹配查询条件
        if (model.getTeacherName() != null && !model.getTeacherName().equals("")) {
            sqlStr.append("and teacher_name = '" + model.getTeacherName() + "' ");
        }
        if (model.getTeacherNum() != null && !model.getTeacherNum().equals("")) {
            sqlStr.append("and teacher_num = '" + model.getTeacherNum() + "' ");
        }
        if (model.getDepartmentId() != null && !model.getDepartmentId().equals("")) {
            sqlStr.append("and department_id = '" + model.getDepartmentId() + "' ");
        }

        Teacher teacher = new Teacher();
        try {
            conn = db.getConnection();
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                teacher.setTeacherId(rs.getLong("teacher_id"));
                teacher.setTeacherName(rs.getString("teacher_name"));
                teacher.setDepartmentId(rs.getString("department_id"));
                teacher.setTeacherFlag(rs.getString("teacher_flag").equals("0") ? "启用" : "禁用");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return teacher;
    }

    @Override
    public boolean isSame(Teacher model, boolean sflag) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select * from pa_teacher where teacher_name = ? ");

        // 更新时排除自身ID
        if (!sflag) {
            sqlStr.append("and teacher_id != " + model.getTeacherId() + " ");
        }

        try {
            conn = db.getConnection();
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setString(1, model.getTeacherName());
            rs = pstm.executeQuery();
            return rs.next();  // 存在同名记录则返回true
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public List<Teacher> getAllDataByPage(Teacher model, Page page) {
        Connection conn = null;// 数据库连接
        PreparedStatement pstm = null;// 预处理语句
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();// 查询语句
        List<Teacher> list = new ArrayList<>();
        sqlStr.append(" SELECT t.*,d.department_name as pdname from pa_teacher t ");
        sqlStr.append(" LEFT JOIN pa_department d on t.department_id=d.department_id ");
        sqlStr.append("where 1=1 ");

        if (model.getTeacherName() != null && !model.getTeacherName().equals("")) {
            sqlStr.append("and teacher_name like '%" + model.getTeacherName() + "%' ");
        }
        if (model.getTeacherFlag() != null && !model.getTeacherFlag().equals("")) {
            sqlStr.append(" and teacher_flag='" + model.getTeacherFlag() + "' ");
        }
//        if (model.getDepartmentPid() != null && !model.getDepartmentPid().equals("")) {
//            sqlStr.append(" and department_pid='" + model.getDepartmentPid() + "' ");
//        }
        sqlStr.append(" ORDER BY teacher_stmp desc,teacher_id asc,teacher_name asc ");
        sqlStr.append(" limit ?,? ");
        conn = db.getConnection();
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            pstm.setInt(1, (page.getCurrentPage() - 1) * page.getCount());// 起始位置
            pstm.setInt(2, page.getCount());
            rs = pstm.executeQuery();// 执行查询操作select
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTeacherId(rs.getLong("teacher_id"));
                teacher.setTeacherName(rs.getString("teacher_name"));
                teacher.setDepartmentId(rs.getString("department_id"));
                teacher.setTeacherFlag(rs.getString("teacher_flag").equals("0") ? "启用" : "禁用");
               teacher.setTeacherGender(rs.getString("teacher_gender"));
               teacher.setTeacherPhone(rs.getString("teacher_phone"));
               teacher.setTeacherNum(rs.getString("teacher_num"));
               teacher.setPdname(rs.getString("pdname"));
               list.add(teacher);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public int getCount(Teacher model) {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select count(*) from pa_teacher t ");
        sqlStr.append("where 1=1 ");

        // 与查询逻辑一致的条件拼接
        if (model.getTeacherName() != null && !model.getTeacherName().equals("")) {
            sqlStr.append("and t.teacher_name like '%" + model.getTeacherName() + "%' ");
        }
        if (model.getTeacherFlag() != null && !model.getTeacherFlag().equals("")) {
            sqlStr.append("and t.teacher_flag = '" + model.getTeacherFlag() + "' ");
        }
        if (model.getDepartmentId() != null && !model.getDepartmentId().equals("")) {
            sqlStr.append("and t.department_id = '" + model.getDepartmentId() + "' ");
        }
        conn = db.getConnection();
        int total = 0;
        try {
            pstm = conn.prepareStatement(sqlStr.toString());
            rs = pstm.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return total;
    }

    @Override
    public List<Teacher> getAllky() {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String sql = "select teacher_id, teacher_name from pa_teacher " +
                "where teacher_flag = '0' order by teacher_name asc";
        List<Teacher> list = new ArrayList<>();

        try {
            conn = db.getConnection();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTeacherId(rs.getLong("teacher_id"));
                teacher.setTeacherName(rs.getString("teacher_name"));
                list.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    }

